package com.rs.util;

import jxl.*;
import jxl.write.*;

import java.io.*;
import java.util.List;
import com.rs.model.Attendance;



public class Excel {
	
	public static void main(String[] args) {
		String day = "2020-08-04";
		int x1 = Integer.parseInt(day.substring(day.length()-2));
		System.out.println(x1);
	}
	public static void aa(String name,int a,String title,int xingqiji,List<Attendance> ll,List<Attendance> l1) throws IOException, WriteException {
		String excelPath = "D:\\"+name+".xls";
		WritableWorkbook book = null;
		WritableSheet ws;
		String[] content = {"休息","请假","旷工","出勤天数","迟到","早退","未打卡","签名"};//excel工作表的标题du
		jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(WritableFont.createFont("微软雅黑"), 12, WritableFont.BOLD);
		jxl.write.WritableFont wfc3 = new jxl.write.WritableFont(WritableFont.createFont("微软雅黑"),10,WritableFont.NO_BOLD);
		try {
			
			File excelFile = new File(excelPath);
			if (!excelFile.exists()) {
			excelFile.createNewFile();
			book = Workbook.createWorkbook(excelFile);
			ws = book.createSheet("用户信息", 0); //新建一个sheet
			//往表中添bai加内容
			ws.setColumnView(0, 15);//1列宽
			ws.setColumnView(1, 9);//2列宽
			for(int j=2;j<(a+2);j++) {
				ws.setColumnView(j,5);//行高
			}
			for(int r=2+a;r<(a+8);r++) {
				ws.setColumnView(r,7);//行高
			}
			ws.setColumnView(9+a,15);//行高
			for(int i=0;i<ll.size()*2;i++) {
				ws.setRowView(i,400);//行高
			}
			
			
			ws.mergeCells(0,0,a+9,1);
			jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.createFont("微软雅黑"), 18, WritableFont.BOLD);
			jxl.write.WritableCellFormat wchB = new jxl.write.WritableCellFormat(wfc);
			wchB.setAlignment(jxl.format.Alignment.CENTRE);
			jxl.write.Label labelCFC = new jxl.write.Label(0,0,title,wchB);
			ws.addCell(labelCFC); 
			
			ws.mergeCells(0,2,0,3);
			jxl.write.WritableCellFormat wchB1 = new jxl.write.WritableCellFormat(wfc1);
			wchB1.setAlignment(jxl.format.Alignment.CENTRE);
			jxl.write.Label labelCFC1 = new jxl.write.Label(0,2,"姓名",wchB1);
			ws.addCell(labelCFC1); 
			
			ws.mergeCells(1,2,1,3);
			jxl.write.WritableCellFormat wchB2 = new jxl.write.WritableCellFormat(wfc1);
			wchB2.setAlignment(jxl.format.Alignment.CENTRE);
			jxl.write.Label labelCFC2 = new jxl.write.Label(1,2,"时间",wchB2);
			ws.addCell(labelCFC2); 
			
			for(int i=0;i<8;i++) {
				ws.mergeCells(2+a+i,2,2+a+i,3);
				jxl.write.WritableCellFormat wchB3 = new jxl.write.WritableCellFormat(wfc3);
				wchB3.setAlignment(jxl.format.Alignment.CENTRE);
				jxl.write.Label labelCFC3 = new jxl.write.Label(2+a+i,2,content[i],wchB3);
				ws.addCell(labelCFC3);
			}
			
			for(int i=0;i<ll.size();i++) {
				ws.mergeCells(0,4+(i*2),0,5+(i*2));
			}
			
			for(int i=0;i<ll.size();i++) {
				for(int j=0;j<8;j++) {
					ws.mergeCells(2+a+j,4+(i*2),2+a+j,5+(i*2));
					}
			}
			int id = 0;
			for(int i=4;i<ll.size()*2+4;i++) {
					String type ="上午";
					jxl.write.WritableCellFormat wchB3 = new jxl.write.WritableCellFormat(wfc3);
					wchB3.setAlignment(jxl.format.Alignment.CENTRE);
					jxl.write.Label labelCFC3 = new jxl.write.Label(1,i,type,wchB3);
					ws.addCell(labelCFC3);
					jxl.write.Label labelCFC4 = new jxl.write.Label(0,i,ll.get(id).getU_name(),wchB3);
					ws.addCell(labelCFC4);
					i++;
					id++;
					type ="下午";
					jxl.write.WritableCellFormat wchB4 = new jxl.write.WritableCellFormat(wfc3);
					wchB4.setAlignment(jxl.format.Alignment.CENTRE);
					jxl.write.Label labelCFC5 = new jxl.write.Label(1,i,type,wchB4);
					ws.addCell(labelCFC5);
			}
			int num = 0;
			String et = "";
			for(int i=2;i<a+2;i++) {
				num++;
				jxl.write.WritableCellFormat wchB3 = new jxl.write.WritableCellFormat(wfc3);
				wchB3.setAlignment(jxl.format.Alignment.CENTRE);
				jxl.write.Label labelCFC3 = new jxl.write.Label(i,3,num+"",wchB3);
				ws.addCell(labelCFC3);
				if(xingqiji==1) {
					et = "一";
				}else if(xingqiji==2) {
					et = "二";
				}else if(xingqiji==3) {
					et = "三";
				}else if(xingqiji==4) {
					et = "四";
				}else if(xingqiji==5) {
					et = "五";
				}else if(xingqiji==6) {
					et = "六";
				}else if(xingqiji==7) {
					et = "日";
				}
				jxl.write.Label labelCFC4 = new jxl.write.Label(i,2,et,wchB3);
				ws.addCell(labelCFC4);
				xingqiji++;
				if(xingqiji==8) {
					xingqiji=1;
				}
		    }
			if(ll.size()!=0) {
				int uid = ll.get(0).getAt_uid();
				int x = 2;
				int y = 4;
				double qj = 0;
				double xxi = 0;
				double kg = 0;
				double cq = 0;
				int cd = 0;
				int zt = 0;
				int wdk = 0;
				String a1="null";
				String b1="null";
				for(Attendance l2:l1) {
					int uid1 = l2.getAt_uid();
					int at_amtype = l2.getAt_amtype();
					int at_nighttype = l2.getat_nighttype();
					String day = new DateFormatUtil().getDateString(l2.getAt_day(),"YYYY-MM-dd");
					int x1 = Integer.parseInt(day.substring(day.length()-2));
					System.out.println("day    "+day);
					if(at_amtype==1) {
						a1="√";
						cq+=0.5;
					}else if(at_amtype==2) {
						a1="※";
						cd+=1;
						cq+=0.5;
					}else if(at_amtype==3) {
						a1="∑";
						zt+=1;
						cq+=0.5;
					}else if(at_amtype==4) {
						a1="■";
						wdk+=1;
						cq+=0.5;
					}else if(at_amtype==5) {
						a1="△";
						qj+=0.5;
					}else if(at_amtype==6) {
						a1="▲";
					}else if(at_amtype==7) {
						a1="∞";
						qj+=0.5;
					}else if(at_amtype==8) {
						a1="∫";
						qj+=0.5;
					}else if(at_amtype==9) {
						a1="∮";
						qj+=0.5;
					}else if(at_amtype==10) {
						a1="○";
						xxi+=0.5;
					}else if(at_amtype==11) {
						a1="⊙";
						xxi+=0.5;
					}else if(at_amtype==12) {
						a1="x";
						kg+=0.5;
					}
					if(at_nighttype==1) {
						b1="√";
						cq+=0.5;
					}else if(at_nighttype==2) {
						b1="※";
						cd+=1;
						cq+=0.5;
					}else if(at_nighttype==3) {
						b1="∑";
						zt+=1;
						cq+=0.5;
					}else if(at_nighttype==4) {
						b1="■";
						wdk+=1;
						cq+=0.5;
					}else if(at_nighttype==5) {
						b1="△";
						qj+=0.5;
					}else if(at_nighttype==6) {
						b1="▲";
					}else if(at_nighttype==7) {
						b1="∞";
						qj+=0.5;
					}else if(at_nighttype==8) {
						b1="∫";
						qj+=0.5;
					}else if(at_nighttype==9) {
						b1="∮";
						qj+=0.5;
					}else if(at_nighttype==10) {
						b1="○";
						xxi+=0.5;
					}else if(at_nighttype==11) {
						b1="⊙";
						xxi+=0.5;
					}else if(at_nighttype==12) {
						b1="x";
						kg+=0.5;
					}
					if(uid1!=uid) {
						 qj = 0;
						 xxi = 0;
						 kg = 0;
						 cq = 0;
						 cd = 0;
						 zt = 0;
						 wdk = 0;
						 if(at_amtype==1) {
								a1="√";
								cq+=0.5;
							}else if(at_amtype==2) {
								a1="※";
								cd+=1;
							}else if(at_amtype==3) {
								a1="∑";
								zt+=1;
							}else if(at_amtype==4) {
								a1="■";
								wdk+=1;
							}else if(at_amtype==5) {
								a1="△";
								qj+=0.5;
							}else if(at_amtype==6) {
								a1="▲";
							}else if(at_amtype==7) {
								a1="∞";
								qj+=0.5;
							}else if(at_amtype==8) {
								a1="∫";
								qj+=0.5;
							}else if(at_amtype==9) {
								a1="∮";
								qj+=0.5;
							}else if(at_amtype==10) {
								a1="○";
								xxi+=0.5;
							}else if(at_amtype==11) {
								a1="⊙";
								xxi+=0.5;
							}else if(at_amtype==12) {
								a1="x";
								kg+=0.5;
							}
							if(at_nighttype==1) {
								b1="√";
								cq+=0.5;
							}else if(at_nighttype==2) {
								b1="※";
								cd+=1;
							}else if(at_nighttype==3) {
								b1="∑";
								zt+=1;
							}else if(at_nighttype==4) {
								b1="■";
								wdk+=1;
							}else if(at_nighttype==5) {
								b1="△";
								qj+=0.5;
							}else if(at_nighttype==6) {
								b1="▲";
							}else if(at_nighttype==7) {
								b1="∞";
								qj+=0.5;
							}else if(at_nighttype==8) {
								b1="∫";
								qj+=0.5;
							}else if(at_nighttype==9) {
								b1="∮";
								qj+=0.5;
							}else if(at_nighttype==10) {
								b1="○";
								xxi+=0.5;
							}else if(at_nighttype==11) {
								b1="⊙";
								xxi+=0.5;
							}else if(at_nighttype==12) {
								b1="x";
								kg+=0.5;
							}
							String[] jg = {xxi+"",qj+"",kg+"",cq+"",cd+"",zt+"",wdk+""};
							uid=uid1;
							y+=2;
							jxl.write.WritableCellFormat wchB3 = new jxl.write.WritableCellFormat(wfc3);
							wchB3.setAlignment(jxl.format.Alignment.CENTRE);
							jxl.write.Label labelCFC3 = new jxl.write.Label(x+x1-1,y,a1,wchB3);
							
							ws.addCell(labelCFC3);
							jxl.write.Label labelCFC4 = new jxl.write.Label(x+x1-1,y+1,b1,wchB3);
							ws.addCell(labelCFC4);
							for(int i=0;i<7;i++) {
								jxl.write.Label labelCFC5 = new jxl.write.Label(2+a+i,y,jg[i],wchB3);
								ws.addCell(labelCFC5);
							}
					}else {
						String[] jg = {xxi+"",qj+"",kg+"",cq+"",cd+"",zt+"",wdk+""};
						jxl.write.WritableCellFormat wchB3 = new jxl.write.WritableCellFormat(wfc3);
						wchB3.setAlignment(jxl.format.Alignment.CENTRE);
						jxl.write.Label labelCFC3 = new jxl.write.Label(x+x1-1,y,a1,wchB3);
						ws.addCell(labelCFC3);
						jxl.write.Label labelCFC4 = new jxl.write.Label(x+x1-1,y+1,b1,wchB3);
						ws.addCell(labelCFC4);
						for(int i=0;i<7;i++) {
							jxl.write.Label labelCFC5 = new jxl.write.Label(2+a+i,y,jg[i],wchB3);
							ws.addCell(labelCFC5);
						}
					}
				}
			}
		 } 
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//将数据写入所建的excel
	    book.write();
	    book.close();
		
	}
	
	 
	
 
}
